#!/home/python/.virtualenvs/equity/bin/python

import sys
sys.path.append('./')
sys.path.insert(0, "../")

import os
if not os.getenv('DJANGO_SETTINGS_MODULE'):
    os.environ['DJANGO_SETTINGS_MODULE'] = 'equity_mall.settings.prod'

import django
django.setup()

from elasticsearch import Elasticsearch, helpers
from django.conf import settings
import pymysql

from orders.models import OrderInfo, OrderGoods


def add_document(body):
    '''新增数据索引'''
    es = Elasticsearch(["http://159.75.55.19:9200/"])
    result = es.create(index="equity_mall_new", doc_type="modelresult", body=body)
    print(result, body['id'])


def delete_document():
    es = Elasticsearch(["http://159.75.55.19:9200/"])
    result = es.delete(index="equity_mall_new", doc_type="modelresult", id='AZMaispTLFYWUti75K8d')
    print(result)


def batch_order_index():
    orderinfo_set = OrderInfo.objects.filter(id__gt=628277, id__lt=8763357).order_by("-id")[:2000000]
    # orderinfo_set = OrderInfo.objects.filter(id=2509)[:1000]
    for orderinfo in orderinfo_set:
        merchant = orderinfo.merchant
        ordergoods_set = OrderGoods.objects.filter(order_id=orderinfo.id)
        level = orderinfo.level
        if orderinfo.customer_user:
            mobile = orderinfo.customer_user.mobile
        elif orderinfo.customeruservice:
            mobile = orderinfo.customeruservice.mobile
        else:
            mobile = ''
        if level:
            prov_id = level.prov_id
            city_id = level.city_id
            district_id = level.district_id
            branch_id = level.branch_id
        else:
            prov_id = ''
            city_id = ''
            district_id = ''
            branch_id = ''
        goods_name = ''
        for ordergoods in ordergoods_set:
            goods = ordergoods.goods
            goods_name += goods.name
        text = f'{merchant.name} {orderinfo.orderNum} {orderinfo.order_id} {mobile} {merchant.wyfMerchantID} {goods_name}'
        body = {
            "id": orderinfo.id,
            "text": text,
            "prov_id": prov_id,
            "city_id": city_id,
            "district_id": district_id,
            "branch_id": branch_id,
            "business_no": merchant.wyfMerchantID
        }
        add_document(body)


def batch_order_index1(a, b):
    conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                           port=settings.DATABASES['default']['PORT'],
                           database=settings.DATABASES['default']['NAME'],
                           user=settings.DATABASES['default']['USER'],
                           password=settings.DATABASES['default']['PASSWORD'])
    cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
    sql = f'SELECT i.*,j.mobile_2 FROM (SELECT g.*,h.mobile_1 FROM (SELECT e.*,f.merchant_name,wyfMerchantID FROM (SELECT c.*,d.prov_id,city_id,district_id,branch_id FROM (SELECT a.*,b.goods_name FROM (SELECT id,merchant_id,level_id,customer_user_id,customeruservice_id,order_id,orderNum FROM tb_order_info WHERE id>{a} AND id<={b} AND id <8763949)AS a LEFT JOIN (SELECT z.order_id,GROUP_CONCAT(z.goods_name)AS goods_name FROM (SELECT x.order_id,y.goods_name FROM (SELECT order_id,goods_id FROM tb_order_goods WHERE order_id>{a} AND order_id<={b} AND order_id <8763949)AS x LEFT JOIN (SELECT id,`name` AS goods_name FROM tb_goods)AS y ON x.goods_id=y.id)AS z GROUP BY z.order_id)AS b ON a.id=b.order_id)AS c LEFT JOIN (SELECT id,prov_id,city_id,district_id,branch_id FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,`name` AS merchant_name,wyfMerchantID FROM tb_merchant)AS f ON e.merchant_id=f.id)AS g LEFT JOIN (SELECT id,mobile AS mobile_1 FROM tb_u_customer_user)AS h ON g.customer_user_id=h.id)AS i LEFT JOIN (SELECT id,mobile AS mobile_2 FROM tb_u_customer_user_vice)AS j ON i.customeruservice_id=j.id;'
    cursor.execute(sql)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    es = Elasticsearch(["http://159.75.55.19:9200/"])
    body = []
    for row in rows:
        if row['mobile_1']:
            mobile = row['mobile_1']
        elif row['mobile_2']:
            mobile = row['mobile_2']
        else:
            mobile = ''
        text = f'{row["merchant_name"]} {row["orderNum"]} {row["order_id"]} {mobile} {row["wyfMerchantID"]} {row["goods_name"]}'
        body.append({
            "_index": "equity_mall_order",
            "_id": row['id'],
            "_type": 'modelresult',
            "action": 'create',
            "_source": {
                "text": text,
                "prov_id": row['prov_id'] if row['prov_id'] else '',
                "city_id": row['city_id'] if row['city_id'] else '',
                "district_id": row['district_id'] if row['district_id'] else '',
                "branch_id": row['branch_id'] if row['branch_id'] else '',
                "business_no": row["wyfMerchantID"]}
        })
    result = helpers.bulk(es, body)
    print(result)


if __name__ == '__main__':
    batch_order_index1(0, 1000)